/*
 * Copyright (c) 2015, Nils Braden
 *
 * This file is part of ttrss-reader-fork. This program is free software; you
 * can redistribute it and/or modify it under the terms of the GNU
 * General Public License as published by the Free Software Foundation;
 * either version 3 of the License, or (at your option) any later
 * version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public License for more details. You should have received a
 * copy of the GNU General Public License along with this program; If
 * not, see http://www.gnu.org/licenses/.
 */

package org.ttrssreader.controllers;

import org.apache.commons.io.FileUtils;
import org.ttrssreader.gui.dialogs.ErrorDialog;
import org.ttrssreader.imageCache.ImageCache;
import org.ttrssreader.model.pojos.Article;
import org.ttrssreader.model.pojos.Category;
import org.ttrssreader.model.pojos.Feed;
import org.ttrssreader.model.pojos.Label;
import org.ttrssreader.model.pojos.RemoteFile;
import org.ttrssreader.utils.AsyncTask;
import org.ttrssreader.utils.StringSupport;
import org.ttrssreader.utils.Utils;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Build;
import android.text.Html;
import android.util.Log;
import android.widget.Toast;

import java.io.File;
import java.io.IOException;
import java.lang.ref.WeakReference;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantReadWriteLock;

public class DBHelper {

    private static final String TAG = DBHelper.class.getSimpleName();

    private static final String DATABASE_NAME = "ttrss.db";
    private static final int DATABASE_VERSION = 60;

    public static final String TABLE_CATEGORIES = "categories";
    public static final String TABLE_FEEDS = "feeds";
    public static final String TABLE_ARTICLES = "articles";
    public static final String TABLE_ARTICLES2LABELS = "articles2labels";
    private static final String TABLE_MARK = "marked";
    public static final String TABLE_REMOTEFILES = "remotefiles";
    public static final String TABLE_REMOTEFILE2ARTICLE = "remotefile2article";

    static final String MARK_READ = "isUnread";
    static final String MARK_STAR = "isStarred";
    static final String MARK_PUBLISH = "isPublished";
    static final String MARK_NOTE = "note";

    // @formatter:off
    private static final String CREATE_TABLE_CATEGORIES =
            "CREATE TABLE "
                    + TABLE_CATEGORIES
                    + " (_id INTEGER PRIMARY KEY,"
                    + " title TEXT,"
                    + " unread INTEGER)";

    private static final String CREATE_TABLE_FEEDS =
            "CREATE TABLE "
                    + TABLE_FEEDS
                    + " (_id INTEGER PRIMARY KEY,"
                    + " categoryId INTEGER,"
                    + " title TEXT,"
                    + " url TEXT,"
                    + " unread INTEGER)";

    private static final String CREATE_TABLE_ARTICLES =
            "CREATE TABLE "
                    + TABLE_ARTICLES
                    + " (_id INTEGER PRIMARY KEY,"
                    + " feedId INTEGER,"
                    + " title TEXT,"
                    + " isUnread INTEGER,"
                    + " articleUrl TEXT,"
                    + " articleCommentUrl TEXT,"
                    + " updateDate INTEGER,"
                    + " content TEXT,"
                    + " attachments TEXT,"
                    + " isStarred INTEGER,"
                    + " isPublished INTEGER,"
                    + " cachedImages INTEGER DEFAULT 0,"
                    + " articleLabels TEXT,"
                    + " author TEXT)";

    private static final String CREATE_TABLE_ARTICLES2LABELS =
            "CREATE TABLE "
                    + TABLE_ARTICLES2LABELS
                    + " (articleId INTEGER,"
                    + " labelId INTEGER, PRIMARY KEY(articleId, labelId))";

    private static final String CREATE_TABLE_MARK =
            "CREATE TABLE "
                    + TABLE_MARK
                    + " (id INTEGER,"
                    + " type INTEGER,"
                    + " " + MARK_READ + " INTEGER,"
                    + " " + MARK_STAR + " INTEGER,"
                    + " " + MARK_PUBLISH + " INTEGER,"
                    + " " + MARK_NOTE + " TEXT,"
                    + " PRIMARY KEY(id, type))";

    private static final String INSERT_CATEGORY =
            "REPLACE INTO "
                    + TABLE_CATEGORIES
                    + " (_id, title, unread)"
                    + " VALUES (?, ?, ?)";

    private static final String INSERT_FEED =
            "REPLACE INTO "
                    + TABLE_FEEDS
                    + " (_id, categoryId, title, url, unread)"
                    + " VALUES (?, ?, ?, ?, ?)";

    private static final String INSERT_ARTICLE =
            "INSERT OR REPLACE INTO "
                    + TABLE_ARTICLES
                    + " (_id, feedId, title, isUnread, articleUrl, articleCommentUrl, updateDate, content, attachments, isStarred, isPublished, cachedImages, articleLabels, author)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, coalesce((SELECT cachedImages FROM " + TABLE_ARTICLES
                    + " WHERE _id=?), NULL), ?, ?)";
    // This should insert new values or replace existing values but should always keep an already inserted value for "cachedImages".
    // When inserting it is set to the default value which is 0 (not "NULL").

    private static final String INSERT_LABEL =
            "REPLACE INTO "
                    + TABLE_ARTICLES2LABELS
                    + " (articleId, labelId)"
                    + " VALUES (?, ?)";

    private static final String INSERT_REMOTEFILE =
            "INSERT OR FAIL INTO "
                    + TABLE_REMOTEFILES
                    + " (url, ext)"
                    + " VALUES (?, ?)";

    private static final String INSERT_REMOTEFILE2ARTICLE =
            "INSERT OR IGNORE INTO "
                    + TABLE_REMOTEFILE2ARTICLE
                    + " (remotefileId, articleId)"
                    + " VALUES (?, ?)";
    // @formatter:on

    WeakReference<Context> contextRef;
    private volatile boolean initialized = false;

    private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock();
    private final Lock r = rwl.readLock();
    private final Lock w = rwl.writeLock();

    private void readLock(boolean lock) {
        if (lock)
            r.lock();
        else
            r.unlock();
    }

    private void writeLock(boolean lock) {
        if (lock)
            w.lock();
        else
            w.unlock();
    }

    private OpenHelper openHelper;

    public synchronized OpenHelper getOpenHelper() {
        return openHelper;
    }

    private final Object insertCategoryLock = new Object();
    private final Object insertFeedLock = new Object();
    private final Object insertArticleLock = new Object();
    private final Object insertLabelLock = new Object();
    private final Object insertRemoteFileLock = new Object();
    private final Object insertRemoteFile2ArticleLock = new Object();

    private SQLiteStatement insertCategory;
    private SQLiteStatement insertFeed;
    private SQLiteStatement insertArticle;
    private SQLiteStatement insertLabel;
    private SQLiteStatement insertRemoteFile;
    private SQLiteStatement insertRemoteFile2Article;

    private static boolean specialUpgradeSuccessful = false;

    // Singleton (see http://stackoverflow.com/a/11165926)
    private DBHelper() {
    }

    private static class InstanceHolder {
        private static final DBHelper instance = new DBHelper();
    }

    public static DBHelper getInstance() {
        return InstanceHolder.instance;
    }

    public synchronized void initialize(final Context context) {
        this.contextRef = new WeakReference<>(context); // TODO: Remove leak of context
        new AsyncTask<Void, Void, Void>() {
            protected Void doInBackground(Void... params) {

                // Check if deleteDB is scheduled or if DeleteOnStartup is set
                if (Controller.getInstance().isDeleteDBScheduled()) {
                    if (deleteDB(context)) {
                        Controller.getInstance().setDeleteDBScheduled(false);
                        initializeDBHelper();
                        return null; // Don't need to check if DB is corrupted, it is NEW!
                    }
                }

                // Initialize DB
                if (!initialized) {
                    initializeDBHelper();
                } else if (getOpenHelper() == null) {
                    initializeDBHelper();
                } else {
                    return null; // DB was already initialized, no need to check anything.
                }

                // Test if DB is accessible, backup and delete if not
                if (initialized) {
                    Cursor c = null;
                    readLock(true);
                    try {
                        // Try to access the DB
                        c = getOpenHelper().getReadableDatabase().rawQuery("SELECT COUNT(*) FROM " + TABLE_CATEGORIES,
                                null);
                        c.getCount();
                        if (c.moveToFirst())
                            c.getInt(0);

                    } catch (Exception e) {
                        Log.e(TAG, "Database was corrupted, creating a new one...", e);
                        closeDB();
                        File dbFile = context.getDatabasePath(DATABASE_NAME);
                        if (dbFile.delete())
                            initializeDBHelper();
                        ErrorDialog
                                .getInstance(
                                        "The Database was corrupted and had to be recreated. If this happened more than once to you please let me know under what circumstances this happened.");
                    } finally {
                        if (c != null && !c.isClosed())
                            c.close();
                        readLock(false);
                    }
                }
                return null;
            }
        }.execute();
    }

    @SuppressWarnings("deprecation")
    private synchronized boolean initializeDBHelper() {
        final Context context = contextRef.get();
        if (context == null) {
            Log.e(TAG, "Can't handle internal DB without Context-Object.");
            return false;
        }

        if (getOpenHelper() != null)
            closeDB();

        openHelper = new OpenHelper(context);
        SQLiteDatabase db = openHelper.getWritableDatabase();

        if (Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN)
            db.setLockingEnabled(true);

        if (specialUpgradeSuccessful) {
            // Re-open DB for final usage:
            closeDB();
            openHelper = new OpenHelper(context);
            db = openHelper.getWritableDatabase();

            Toast.makeText(context, "ImageCache is beeing cleaned...", Toast.LENGTH_LONG).show();
            new org.ttrssreader.utils.AsyncTask<Void, Void, Void>() {
                protected Void doInBackground(Void... params) {
                    // Clear ImageCache since no files are in REMOTE_FILES anymore and we dont want to leave them
                    // there forever:
                    ImageCache imageCache = Controller.getInstance().getImageCache();
                    imageCache.fillMemoryCacheFromDisk();
                    File cacheFolder = new File(imageCache.getDiskCacheDirectory());
                    if (cacheFolder.isDirectory()) {
                        try {
                            FileUtils.deleteDirectory(cacheFolder);
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    return null;
                }

                protected void onPostExecute(Void result) {
                    Toast.makeText(context, "ImageCache has been cleaned up...", Toast.LENGTH_LONG).show();
                }
            }.execute();
        }

        insertCategory = db.compileStatement(INSERT_CATEGORY);
        insertFeed = db.compileStatement(INSERT_FEED);
        insertArticle = db.compileStatement(INSERT_ARTICLE);
        insertLabel = db.compileStatement(INSERT_LABEL);
        insertRemoteFile = db.compileStatement(INSERT_REMOTEFILE);
        insertRemoteFile2Article = db.compileStatement(INSERT_REMOTEFILE2ARTICLE);

        db.acquireReference();
        initialized = true;
        return true;
    }

    private synchronized boolean deleteDB(final Context context) {
        if (context == null)
            return false;

        Log.i(TAG, "Deleting Database as requested by preferences.");
        File f = context.getDatabasePath(DATABASE_NAME);
        if (f.exists()) {
            if (getOpenHelper() != null) {
                closeDB();
            }
            return f.delete();
        }

        return false;
    }

    private synchronized void closeDB() {
        writeLock(true);
        try {
            getOpenHelper().close();
            openHelper = null;
        } finally {
            writeLock(false);
        }
    }

    private synchronized boolean isDBAvailable() {
        if (getOpenHelper() != null) {
            return true;
        } else {
            Log.i(TAG, "Controller not initialized, trying to do that now...");
            return initializeDBHelper();
        }
    }

    public static class OpenHelper extends SQLiteOpenHelper {

        public OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        /**
         * set wished DB modes on DB
         *
         * @param db DB to be used
         */
        @Override
        public void onOpen(SQLiteDatabase db) {
            super.onOpen(db);
            if (!db.isReadOnly()) {
                // Enable foreign key constraints
                db.execSQL("PRAGMA foreign_keys=ON;");
            }
        }

        /**
         * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_CATEGORIES);
            db.execSQL(CREATE_TABLE_FEEDS);
            db.execSQL(CREATE_TABLE_ARTICLES);
            db.execSQL(CREATE_TABLE_ARTICLES2LABELS);
            db.execSQL(CREATE_TABLE_MARK);
            createRemoteFilesSupportDBObjects(db);
        }

        /**
         * upgrade the DB
         *
         * @param db         The database.
         * @param oldVersion The old database version.
         * @param newVersion The new database version.
         * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            boolean didUpgrade = false;

            if (oldVersion < 40) {
                String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN isStarred INTEGER";

                Log.i(TAG, String.format("Upgrading database from %s to 40.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 42) {
                String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN isPublished INTEGER";

                Log.i(TAG, String.format("Upgrading database from %s to 42.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 45) {
                // @formatter:off
                String sql = "CREATE TABLE IF NOT EXISTS "
                        + TABLE_MARK
                        + " (id INTEGER,"
                        + " type INTEGER,"
                        + " " + MARK_READ + " INTEGER,"
                        + " " + MARK_STAR + " INTEGER,"
                        + " " + MARK_PUBLISH + " INTEGER,"
                        + " PRIMARY KEY(id, type))";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 45.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 46) {

                // @formatter:off
                String sql = "DROP TABLE IF EXISTS "
                        + TABLE_MARK;
                String sql2 = "CREATE TABLE IF NOT EXISTS "
                        + TABLE_MARK
                        + " (id INTEGER PRIMARY KEY,"
                        + " " + MARK_READ + " INTEGER,"
                        + " " + MARK_STAR + " INTEGER,"
                        + " " + MARK_PUBLISH + " INTEGER)";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 46.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));
                Log.i(TAG, String.format(" (Executing: %s", sql2));

                db.execSQL(sql);
                db.execSQL(sql2);
                didUpgrade = true;
            }

            if (oldVersion < 47) {
                String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN cachedImages INTEGER DEFAULT 0";

                Log.i(TAG, String.format("Upgrading database from %s to 47.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 48) {
                // @formatter:off
                String sql = "CREATE TABLE IF NOT EXISTS "
                        + TABLE_MARK
                        + " (id INTEGER,"
                        + " type INTEGER,"
                        + " " + MARK_READ + " INTEGER,"
                        + " " + MARK_STAR + " INTEGER,"
                        + " " + MARK_PUBLISH + " INTEGER,"
                        + " PRIMARY KEY(id, type))";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 48.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 49) {
                // @formatter:off
                String sql = "CREATE TABLE "
                        + TABLE_ARTICLES2LABELS
                        + " (articleId INTEGER,"
                        + " labelId INTEGER, PRIMARY KEY(articleId, labelId))";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 49.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 50) {
                Log.i(TAG, String.format("Upgrading database from %s to 50.", oldVersion));
                ContentValues cv = new ContentValues(1);
                cv.put("cachedImages", 0);
                db.update(TABLE_ARTICLES, cv, "cachedImages IS null", null);
                didUpgrade = true;
            }

            if (oldVersion < 51) {
                // @formatter:off
                String sql = "DROP TABLE IF EXISTS "
                        + TABLE_MARK;
                String sql2 = "CREATE TABLE "
                        + TABLE_MARK
                        + " (id INTEGER,"
                        + " type INTEGER,"
                        + " " + MARK_READ + " INTEGER,"
                        + " " + MARK_STAR + " INTEGER,"
                        + " " + MARK_PUBLISH + " INTEGER,"
                        + " " + MARK_NOTE + " TEXT,"
                        + " PRIMARY KEY(id, type))";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 51.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));
                Log.i(TAG, String.format(" (Executing: %s", sql2));

                db.execSQL(sql);
                db.execSQL(sql2);
                didUpgrade = true;
            }

            if (oldVersion < 52) {
                // @formatter:off
                String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN articleLabels TEXT";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 52.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 53) {
                Log.i(TAG, String.format("Upgrading database from %s to 53.", oldVersion));
                didUpgrade = createRemoteFilesSupportDBObjects(db);
                if (didUpgrade) {
                    ContentValues cv = new ContentValues(1);
                    cv.putNull("cachedImages");
                    db.update(TABLE_ARTICLES, cv, null, null);
                    ImageCache ic = Controller.getInstance().getImageCache();
                    if (ic != null) {
                        ic.clear();
                    }
                }
            }

            if (oldVersion < 58) {
                Log.i(TAG, String.format("Upgrading database from %s to 58.", oldVersion));

                // Rename columns "id" to "_id" by modifying the table structure:
                db.beginTransaction();
                try {
                    db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILES);
                    db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILE2ARTICLE);

                    db.execSQL("PRAGMA writable_schema=1;");
                    String sql = "UPDATE SQLITE_MASTER SET SQL = '%s' WHERE NAME = '%s';";
                    db.execSQL(String.format(sql, CREATE_TABLE_CATEGORIES, TABLE_CATEGORIES));
                    db.execSQL(String.format(sql, CREATE_TABLE_FEEDS, TABLE_FEEDS));
                    db.execSQL(String.format(sql, CREATE_TABLE_ARTICLES, TABLE_ARTICLES));
                    db.execSQL("PRAGMA writable_schema=0;");

                    if (createRemoteFilesSupportDBObjects(db)) {
                        db.setTransactionSuccessful();
                        didUpgrade = true;
                    }
                } finally {
                    db.execSQL("PRAGMA foreign_keys=ON;");
                    db.endTransaction();
                    specialUpgradeSuccessful = true;
                }
            }

            if (oldVersion < 59) {
                // @formatter:off
                String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN author TEXT";
                // @formatter:on

                Log.i(TAG, String.format("Upgrading database from %s to 59.", oldVersion));
                Log.i(TAG, String.format(" (Executing: %s", sql));

                db.execSQL(sql);
                didUpgrade = true;
            }

            if (oldVersion < 60) {
                Log.i(TAG, String.format("Upgrading database from %s to 59.", oldVersion));
                Log.i(TAG, String.format(" (Re-Creating View: remotefiles_sequence )"));

                createRemotefilesView(db);
                didUpgrade = true;
            }

            if (!didUpgrade) {
                Log.i(TAG, "Upgrading database, this will drop tables and recreate.");
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_FEEDS);
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_ARTICLES);
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_MARK);
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILES);
                onCreate(db);
            }

        }

        /**
         * create DB objects (tables, triggers, views) which
         * are necessary for file cache support
         *
         * @param db current database
         */
        private boolean createRemoteFilesSupportDBObjects(SQLiteDatabase db) {
            boolean success = false;
            try {
                createRemotefiles(db);
                createRemotefiles2Articles(db);
                createRemotefilesView(db);
                success = true;
            } catch (SQLException e) {
                Log.e(TAG, "Creation of remote file support DB objects failed.\n" + e);
            }

            return success;
        }

        private void createRemotefiles(SQLiteDatabase db) {
            // @formatter:off
            // remote files (images, attachments, etc) belonging to articles,
            // which are locally stored (cached)
            db.execSQL("CREATE TABLE "
                    + TABLE_REMOTEFILES
                    + " (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    // remote file URL
                    + " url TEXT UNIQUE NOT NULL,"
                    // file size
                    + " length INTEGER DEFAULT 0,"
                    // extension - some kind of additional info
                    // (i.e. file extension)
                    + " ext TEXT NOT NULL,"
                    // unix timestamp of last change
                    // (set automatically by triggers)
                    + " updateDate INTEGER,"
                    // boolean flag determining if the file is locally stored
                    + " cached INTEGER DEFAULT 0)");

            // index for quiicker search by by URL
            db.execSQL("DROP INDEX IF EXISTS idx_remotefiles_by_url");
            db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS idx_remotefiles_by_url"
                    + " ON " + TABLE_REMOTEFILES
                    + " (url)");

            // sets last change unix timestamp after row creation
            db.execSQL("DROP TRIGGER IF EXISTS insert_remotefiles");
            db.execSQL("CREATE TRIGGER IF NOT EXISTS insert_remotefiles AFTER INSERT"
                    + " ON " + TABLE_REMOTEFILES
                    + "   BEGIN"
                    + "     UPDATE " + TABLE_REMOTEFILES
                    + "       SET updateDate = strftime('%s', 'now')"
                    + "     WHERE id = new.id;"
                    + "   END");

            // sets last change unix timestamp after row update
            db.execSQL("DROP TRIGGER IF EXISTS update_remotefiles_lastchanged");
            db.execSQL("CREATE TRIGGER IF NOT EXISTS update_remotefiles_lastchanged AFTER UPDATE"
                    + " ON " + TABLE_REMOTEFILES
                    + "   BEGIN"
                    + "     UPDATE " + TABLE_REMOTEFILES
                    + "       SET updateDate = strftime('%s', 'now')"
                    + "     WHERE id = new.id;"
                    + "   END");

            // @formatter:on
        }

        private void createRemotefiles2Articles(SQLiteDatabase db) {
            // @formatter:off
            // m to n relations between articles and remote files
            db.execSQL("CREATE TABLE "
                    + TABLE_REMOTEFILE2ARTICLE
                    // ID of remote file
                    + "(remotefileId INTEGER"
                    + "   REFERENCES " + TABLE_REMOTEFILES + "(id)"
                    + "     ON DELETE CASCADE,"
                    // ID of article
                    + " articleId INTEGER"
                    + "   REFERENCES " + TABLE_ARTICLES + "(_id)"
                    + "     ON UPDATE CASCADE"
                    + "     ON DELETE NO ACTION,"
                    // if both IDs are known, then the row should be found faster
                    + " PRIMARY KEY(remotefileId, articleId))");

            // update count of cached images for article on change of "cached"
            // field of remotefiles
            db.execSQL("DROP TRIGGER IF EXISTS update_remotefiles_articlefiles");
            db.execSQL("CREATE TRIGGER IF NOT EXISTS update_remotefiles_articlefiles AFTER UPDATE"
                    + " OF cached"
                    + " ON " + TABLE_REMOTEFILES
                    + "   BEGIN"
                    + "     UPDATE " + TABLE_ARTICLES + ""
                    + "       SET"
                    + "         cachedImages = ("
                    + "           SELECT"
                    + "             COUNT(r.id)"
                    + "           FROM " + TABLE_REMOTEFILES + " r,"
                    + TABLE_REMOTEFILE2ARTICLE + " m"
                    + "           WHERE"
                    + "             m.remotefileId=r.id"
                    + "             AND m.articleId=" + TABLE_ARTICLES + "._id"
                    + "             AND r.cached=1)"
                    + "       WHERE _id IN ("
                    + "         SELECT"
                    + "           a._id"
                    + "         FROM " + TABLE_REMOTEFILE2ARTICLE + " m,"
                    + TABLE_ARTICLES + " a"
                    + "         WHERE"
                    + "           m.remotefileId=new.id AND m.articleId=a._id);"
                    + "   END");
            // @formatter:on
        }

        private void createRemotefilesView(SQLiteDatabase db) {
            // @formatter:off
            // represents importance of cached files
            // the sequence is defined by
            // 1. the article to which the remote file belongs to is not read
            // 2. update date of the article to which the remote file belongs to
            // 3. the file length
            db.execSQL("DROP VIEW IF EXISTS remotefile_sequence");
            db.execSQL("CREATE VIEW IF NOT EXISTS remotefile_sequence AS"
                    + " SELECT r.*, MAX(a.isUnread) AS isUnread,"
                    + "   MAX(a.updateDate) AS articleUpdateDate,"
                    + "   MAX(a.isUnread)||MAX(a.updateDate)||(100000000000-r.length)"
                    + "     AS ord"
                    + " FROM " + TABLE_REMOTEFILES + " r,"
                    + TABLE_REMOTEFILE2ARTICLE + " m,"
                    + TABLE_ARTICLES + " a"
                    + " WHERE m.remotefileId=r.id AND m.articleId=a._id"
                    + " GROUP BY r.id");
            // @formatter:on
        }

    }

    // *******| INSERT |*******************************************************************

    private void insertCategory(int id, String title, int unread) {
        if (title == null)
            title = "";

        synchronized (insertCategoryLock) {
            insertCategory.bindLong(1, id);
            insertCategory.bindString(2, title);
            insertCategory.bindLong(3, unread);

            if (!isDBAvailable())
                return;
            insertCategory.execute();
        }
    }

    void insertCategories(Set<Category> set) {
        if (!isDBAvailable() || set == null)
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (Category c : set) {
                insertCategory(c.id, c.title, c.unread);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    private void insertFeed(int id, int categoryId, String title, String url, int unread) {
        if (title == null)
            title = "";
        if (url == null)
            url = "";

        synchronized (insertFeedLock) {
            insertFeed.bindLong(1, Integer.valueOf(id).longValue());
            insertFeed.bindLong(2, Integer.valueOf(categoryId).longValue());
            insertFeed.bindString(3, title);
            insertFeed.bindString(4, url);
            insertFeed.bindLong(5, unread);

            if (!isDBAvailable())
                return;
            insertFeed.execute();
        }
    }

    void insertFeeds(Set<Feed> set) {
        if (!isDBAvailable() || set == null)
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (Feed f : set) {
                insertFeed(f.id, f.categoryId, f.title, f.url, f.unread);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    private void insertArticleIntern(Article a) {
        if (a.title == null)
            a.title = "";
        if (a.content == null)
            a.content = "";
        if (a.url == null)
            a.url = "";
        if (a.commentUrl == null)
            a.commentUrl = "";
        if (a.updated == null)
            a.updated = new Date();
        if (a.attachments == null)
            a.attachments = new LinkedHashSet<>();
        if (a.labels == null)
            a.labels = new LinkedHashSet<>();
        if (a.author == null)
            a.author = "";

        // articleLabels
        long retId;
        synchronized (insertArticleLock) {
            insertArticle.bindLong(1, a.id);
            insertArticle.bindLong(2, a.feedId);
            insertArticle.bindString(3, Html.fromHtml(a.title).toString());
            insertArticle.bindLong(4, (a.isUnread ? 1 : 0));
            insertArticle.bindString(5, a.url);
            insertArticle.bindString(6, a.commentUrl);
            insertArticle.bindLong(7, a.updated.getTime());
            insertArticle.bindString(8, a.content);
            insertArticle.bindString(9, Utils.separateItems(a.attachments, ";"));
            insertArticle.bindLong(10, (a.isStarred ? 1 : 0));
            insertArticle.bindLong(11, (a.isPublished ? 1 : 0));
            insertArticle.bindLong(12, a.id); // ID again for the where-clause
            insertArticle.bindString(13, Utils.separateItems(a.labels, "---"));
            insertArticle.bindString(14, a.author);

            if (!isDBAvailable())
                return;
            retId = insertArticle.executeInsert();
        }

        if (retId != -1)
            insertLabels(a.id, a.labels);
    }

    void insertArticles(Collection<Article> articles) {
        if (!isDBAvailable() || articles == null || articles.isEmpty())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (Article a : articles) {
                insertArticleIntern(a);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    private void insertLabels(int articleId, Set<Label> labels) {
        for (Label label : labels) {
            insertLabel(articleId, label);
        }
    }

    private void insertLabel(int articleId, Label label) {
        if (!isDBAvailable())
            return;

        if (label.id < -10) {
            synchronized (insertLabelLock) {
                insertLabel.bindLong(1, articleId);
                insertLabel.bindLong(2, label.id);
                insertLabel.executeInsert();
            }
        }
    }

    private void removeLabel(int articleId, Label label) {
        if (!isDBAvailable())
            return;

        if (label.id < -10) {
            String[] args = new String[]{articleId + "", label.id + ""};

            SQLiteDatabase db = getOpenHelper().getWritableDatabase();
            writeLock(true);
            try {
                db.delete(TABLE_ARTICLES2LABELS, "articleId=? AND labelId=?", args);
            } finally {
                writeLock(false);
            }
        }
    }

    void insertLabels(Set<Integer> articleIds, Label label, boolean assign) {
        if (!isDBAvailable())
            return;

        for (Integer articleId : articleIds) {
            if (assign)
                insertLabel(articleId, label);
            else
                removeLabel(articleId, label);
        }
    }

    /**
     * insert given remote file into DB
     *
     * @param url remote file URL
     * @return remote file id, which was inserted or already exist in DB
     */
    private long insertRemoteFile(String url) {
        long ret = 0;

        try {
            synchronized (insertRemoteFileLock) {
                insertRemoteFile.bindString(1, url);
                // extension (reserved for future)
                insertRemoteFile.bindString(2, "");

                if (isDBAvailable())
                    ret = insertRemoteFile.executeInsert();
            }
        } catch (SQLException e) {
            // if this remote file already in DB, get its ID
            ret = getRemoteFile(url).id;
        }

        return ret;
    }

    /**
     * insert given relation (remotefileId <-> articleId) into DB
     *
     * @param rfId remote file ID
     * @param aId  article ID
     */
    private void insertRemoteFile2Article(long rfId, long aId) {
        synchronized (insertRemoteFile2ArticleLock) {
            insertRemoteFile2Article.bindLong(1, rfId);
            // extension (reserved for future)
            insertRemoteFile2Article.bindLong(2, aId);

            if (isDBAvailable())
                insertRemoteFile2Article.executeInsert();
        }
    }

    // *******| UPDATE |*******************************************************************

    /**
     * set read status in DB for given category/feed
     *
     * @param id         category/feed ID
     * @param isCategory if set to {@code true}, then given id is category
     *                   ID, otherwise - feed ID
     * @return collection of article IDs, which was marked as read or {@code null} if nothing was changed
     */
    Collection<Integer> markRead(int id, boolean isCategory) {
        Set<Integer> ret = null;
        if (!isDBAvailable())
            return null;

        StringBuilder where = new StringBuilder();
        StringBuilder feedIds = new StringBuilder();
        switch (id) {
            case Data.VCAT_ALL:
                where.append(" 1 "); // Select everything...
                break;
            case Data.VCAT_FRESH:
                long time = System.currentTimeMillis() - Controller.getInstance().getFreshArticleMaxAge();
                where.append(" updateDate > ").append(time);
                break;
            case Data.VCAT_PUB:
                where.append(" isPublished > 0 ");
                break;
            case Data.VCAT_STAR:
                where.append(" isStarred > 0 ");
                break;
            default:
                if (isCategory) {
                    feedIds.append("SELECT _id FROM ").append(TABLE_FEEDS).append(" WHERE categoryId=").append(id);
                } else {
                    feedIds.append(id);
                }
                where.append(" feedId IN (").append(feedIds).append(") ");
                break;
        }

        where.append(" and isUnread>0 ");

        Cursor c = null;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        try {
            // select id from articles where categoryId in (...)
            c = db.query(TABLE_ARTICLES, new String[]{"_id"}, where.toString(), null, null, null, null);

            int count = c.getCount();
            if (count > 0) {
                ret = new HashSet<>(count);
                while (c.moveToNext()) {
                    ret.add(c.getInt(0));
                }
            }

        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }

        if (ret != null && !ret.isEmpty()) {
            // TODO Check access
            markArticles(ret, "isUnread", 0);
        }

        return ret;
    }

    /**
     * mark given property of given articles with given state
     *
     * @param idList set of article IDs, which should be processed
     * @param mark   mark to be set
     * @param state  value for the mark
     */
    public void markArticles(Set<Integer> idList, String mark, int state) {
        if (!isDBAvailable())
            return;

        if (idList != null && !idList.isEmpty()) {
            SQLiteDatabase db = getOpenHelper().getWritableDatabase();
            writeLock(true);
            db.beginTransaction();
            try {
                for (String ids : StringSupport.convertListToString(idList, 400)) {
                    markArticles(ids, mark, state);
                }
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
                writeLock(false);
            }
        }
    }

    /**
     * mark given property of given article with given state
     *
     * @param id    set of article IDs, which should be processed
     * @param mark  mark to be set
     * @param state value for the mark
     */
    public void markArticle(int id, String mark, int state) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            markArticles("" + id, mark, state);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    /**
     * mark given property of given articles with given state
     *
     * @param idList set of article IDs, which should be processed
     * @param mark   mark to be set
     * @param state  value for the mark
     * @return the number of rows affected
     */
    private int markArticles(String idList, String mark, int state) {
        int ret = 0;
        if (!isDBAvailable())
            return ret;

        ContentValues cv = new ContentValues(1);
        cv.put(mark, state);

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            ret = db.update(TABLE_ARTICLES, cv, "_id IN (" + idList + ") AND ? != ?",
                    new String[]{mark, String.valueOf(state)});
        } finally {
            writeLock(false);
        }

        return ret;
    }

    void markUnsynchronizedStates(Collection<Integer> ids, String mark, int state) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (Integer id : ids) {
                // First update, then insert. If row exists it gets updated and second call ignores it, else the second
                // call inserts it.
                db.execSQL(String.format("UPDATE %s SET %s=%s WHERE id=%s", TABLE_MARK, mark, state, id));
                db.execSQL(String.format("INSERT OR IGNORE INTO %s (id, %s) VALUES (%s, %s)", TABLE_MARK, mark, id,
                        state));
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    // Special treatment for notes since the method markUnsynchronizedStates(...) doesn't support inserting any
    // additional data.
    void markUnsynchronizedNotes(Map<Integer, String> ids) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (Integer id : ids.keySet()) {
                String note = ids.get(id);
                if (note == null || note.equals(""))
                    continue;

                ContentValues cv = new ContentValues(1);
                cv.put(MARK_NOTE, note);
                db.update(TABLE_MARK, cv, "id=" + id, null);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    /**
     * Set unread counters for feeds and categories according to real amount of unread articles.
     */
    void calculateCounters() {
        if (!isDBAvailable())
            return;

        long time = System.currentTimeMillis();
        int total = 0;
        Cursor c = null;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues(1);

            // First of all, reset all feeds and all categories to unread=0
            cv.put("unread", 0);
            db.update(TABLE_FEEDS, cv, null, null);
            db.update(TABLE_CATEGORIES, cv, null, null);

            // Count all feeds where unread articles exist
            try {
                // select feedId, count(*) from articles where isUnread>0 group by feedId
                c = db.query(TABLE_ARTICLES, new String[]{"feedId", "count(*)"}, "isUnread>0", null, "feedId", null,
                        null, null);

                // update feeds
                while (c.moveToNext()) {
                    int feedId = c.getInt(0);
                    int unreadCount = c.getInt(1);

                    total += unreadCount;

                    cv.put("unread", unreadCount);
                    db.update(TABLE_FEEDS, cv, "_id=" + feedId, null);
                }
            } finally {
                if (c != null && !c.isClosed())
                    c.close();
            }

            // Count all categories where feeds with unread articles exist
            try {
                // select categoryId, sum(unread) from feeds where categoryId >= 0 group by categoryId
                c = db.query(TABLE_FEEDS, new String[]{"categoryId", "sum(unread)"}, "categoryId>=0", null,
                        "categoryId", null, null, null);

                // update real categories
                while (c.moveToNext()) {
                    int categoryId = c.getInt(0);
                    int unreadCount = c.getInt(1);

                    cv.put("unread", unreadCount);
                    db.update(TABLE_CATEGORIES, cv, "_id=" + categoryId, null);
                }
            } finally {
                if (c != null && !c.isClosed())
                    c.close();
            }

            // Count special categories
            cv.put("unread", total);
            db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_ALL, null);

            cv.put("unread", getUnreadCount(Data.VCAT_FRESH, true));
            db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_FRESH, null);

            cv.put("unread", getUnreadCount(Data.VCAT_PUB, true));
            db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_PUB, null);

            cv.put("unread", getUnreadCount(Data.VCAT_STAR, true));
            db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_STAR, null);

            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }

        Log.i(TAG, String.format("Fixed counters, total unread: %s (took %sms)", total,
                (System.currentTimeMillis() - time)));
    }

    /**
     * update amount of remote file references for article.
     * normally should only be used with {@code null} ("unknown") and {@code 0} (no references)
     *
     * @param id         ID of article, which should be updated
     * @param filesCount new value for remote file references (may be {@code null})
     */
    public void updateArticleCachedImages(int id, Integer filesCount) {
        if (!isDBAvailable())
            return;

        ContentValues cv = new ContentValues(1);
        if (filesCount == null)
            cv.putNull("cachedImages");
        else
            cv.put("cachedImages", filesCount);

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            db.update(TABLE_ARTICLES, cv, "_id=?", new String[]{String.valueOf(id)});
        } finally {
            writeLock(false);
        }
    }

    void deleteCategories(boolean withVirtualCategories) {
        if (!isDBAvailable())
            return;

        String wherePart = "";
        if (!withVirtualCategories)
            wherePart = "_id > 0";

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            db.delete(TABLE_CATEGORIES, wherePart, null);
        } finally {
            writeLock(false);
        }
    }

    /**
     * delete all rows from feeds table
     */
    void deleteFeeds() {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            db.delete(TABLE_FEEDS, null, null);
        } finally {
            writeLock(false);
        }
    }

    /**
     * delete articles and all its resources (e.g. remote files, labels etc.)
     *
     * @param whereClause the optional WHERE clause to apply when deleting.
     *                    Passing null will delete all rows.
     * @param whereArgs   You may include ?s in the where clause, which
     *                    will be replaced by the values from whereArgs. The values
     *                    will be bound as Strings.
     * @return the number of rows affected if a whereClause is passed in, 0
     * otherwise. To remove all rows and get a count pass "1" as the
     * whereClause.
     */
    private int safelyDeleteArticles(String whereClause, String[] whereArgs) {
        int deletedCount = 0;

        Collection<RemoteFile> rfs = getRemoteFilesForArticles(whereClause, whereArgs, true);
        if (!rfs.isEmpty()) {
            Set<Integer> rfIds = new HashSet<>(rfs.size());
            for (RemoteFile rf : rfs) {
                rfIds.add(rf.id);
                Controller.getInstance().getImageCache().getCacheFile(rf.url).delete();
            }
            deleteRemoteFiles(rfIds);
        }

        // @formatter:off
        StringBuilder query = new StringBuilder();
        query.append(
                " articleId IN (").append(
                "     SELECT _id").append(
                "       FROM ").append(
                TABLE_ARTICLES).append(
                "       WHERE ").append(
                whereClause).append(
                " )");
        // @formatter:on

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            // first, delete article referencies from linking table to preserve foreign key constraint on the next step
            db.delete(TABLE_REMOTEFILE2ARTICLE, query.toString(), whereArgs);

            // TODO Foreign-key constraint failed from purgeOrphanedArticles() and safelyDeleteArticles()
            deletedCount = db.delete(TABLE_ARTICLES, whereClause, whereArgs);
            purgeLabels();
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }

        return deletedCount;
    }

    /**
     * Delete given amount of last updated articles from DB. Published and Starred articles are ignored
     * so the configured limit is not an exact upper limit to the number of articles in the database.
     *
     * @param amountToPurge amount of articles to be purged
     */
    void purgeLastArticles(int amountToPurge) {
        if (!isDBAvailable())
            return;

        long time = System.currentTimeMillis();
        String query = "_id IN ( SELECT _id FROM " + TABLE_ARTICLES
                + " WHERE isPublished=0 AND isStarred=0 ORDER BY updateDate DESC LIMIT -1 OFFSET "
                + (Utils.ARTICLE_LIMIT - amountToPurge + ")");

        safelyDeleteArticles(query, null);
        Log.d(TAG, "purgeLastArticles took " + (System.currentTimeMillis() - time) + "ms");
    }

    /**
     * delete articles, which belongs to non-existent feeds
     */
    void purgeOrphanedArticles() {
        if (!isDBAvailable())
            return;

        long time = System.currentTimeMillis();
        safelyDeleteArticles("feedId NOT IN (SELECT _id FROM " + TABLE_FEEDS + ")", null);
        Log.d(TAG, "purgeOrphanedArticles took " + (System.currentTimeMillis() - time) + "ms");
    }

    private void purgeLabels() {
        if (!isDBAvailable())
            return;

        // @formatter:off
        String idsArticles = "SELECT a2l.articleId FROM "
                + TABLE_ARTICLES2LABELS + " AS a2l LEFT OUTER JOIN "
                + TABLE_ARTICLES + " AS a"
                + " ON a2l.articleId = a._id WHERE a._id IS null";

        String idsFeeds = "SELECT a2l.labelId FROM "
                + TABLE_ARTICLES2LABELS + " AS a2l LEFT OUTER JOIN "
                + TABLE_FEEDS + " AS f"
                + " ON a2l.labelId = f._id WHERE f._id IS null";
        // @formatter:on

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            db.delete(TABLE_ARTICLES2LABELS, "articleId IN(" + idsArticles + ")", null);
            db.delete(TABLE_ARTICLES2LABELS, "labelId IN(" + idsFeeds + ")", null);
        } finally {
            writeLock(false);
        }
    }

    void handlePurgeMarked(String idList, int minId, String vcat) {
        if (!isDBAvailable())
            return;

        long time = System.currentTimeMillis();
        ContentValues cv = new ContentValues(1);
        cv.put(vcat, 0);

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        try {
            int count = db.update(TABLE_ARTICLES, cv,
                    vcat + ">0 AND _id>" + minId + " AND _id NOT IN (" + idList + ")", null);
            long timeDiff = (System.currentTimeMillis() - time);
            Log.d(TAG, String.format("Marked %s articles %s=0 (%s ms)", count, vcat, timeDiff));
        } finally {
            writeLock(false);
        }
    }

    // *******| SELECT |*******************************************************************

    public Article getArticle(int id) {
        Article ret = null;
        if (!isDBAvailable())
            return null;

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_ARTICLES, null, "_id=?", new String[]{id + ""}, null, null, null, null);
            if (c.moveToFirst())
                ret = handleArticleCursor(c);
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }

        return ret;
    }

    Set<Label> getLabelsForArticle(int articleId) {
        if (!isDBAvailable())
            return new HashSet<>();

        // @formatter:off
        String sql = "SELECT f._id, f.title, 0 checked FROM " + TABLE_FEEDS + " f "
                + "     WHERE f._id <= -11 AND"
                + "     NOT EXISTS (SELECT * FROM " + TABLE_ARTICLES2LABELS
                + " a2l where f._id = a2l.labelId AND a2l.articleId = " + articleId + ")"
                + " UNION"
                + " SELECT f._id, f.title, 1 checked FROM " + TABLE_FEEDS + " f, " + TABLE_ARTICLES2LABELS + " a2l "
                + "     WHERE f._id <= -11 AND f._id = a2l.labelId AND a2l.articleId = " + articleId;
        // @formatter:on

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.rawQuery(sql, null);
            Set<Label> ret = new HashSet<>(c.getCount());
            while (c.moveToNext()) {
                Label label = new Label();
                label.id = c.getInt(0);
                label.caption = c.getString(1);
                label.checked = c.getInt(2) == 1;
                ret.add(label);
            }
            return ret;

        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
    }

    public Feed getFeed(int id) {
        Feed ret = new Feed();
        if (!isDBAvailable())
            return ret;

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_FEEDS, null, "_id=?", new String[]{id + ""}, null, null, null, null);
            if (c.moveToFirst())
                ret = handleFeedCursor(c);
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }

        return ret;
    }

    public Category getCategory(int id) {
        Category ret = new Category();
        if (!isDBAvailable())
            return ret;

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_CATEGORIES, null, "_id=?", new String[]{id + ""}, null, null, null, null);
            if (c.moveToFirst())
                ret = handleCategoryCursor(c);
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }

        return ret;
    }

    /**
     * get the map of article IDs to its update date from DB
     *
     * @param selection     A filter declaring which articles should be considered, formatted as an SQL WHERE clause
     *                      (excluding
     *                      the WHERE
     *                      itself). Passing null will return all rows.
     * @param selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs,
     *                      in
     *                      order
     *                      that they appear in the selection. The values will be bound as Strings.
     * @return map of unread article IDs to its update date (may be {@code null})
     */
    @SuppressLint("UseSparseArrays")
    public Map<Integer, Long> getArticleIdUpdatedMap(String selection, String[] selectionArgs) {
        Map<Integer, Long> ret = null;
        if (!isDBAvailable())
            return null;

        Cursor c = null;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        try {
            c = db.query(TABLE_ARTICLES, new String[]{"_id", "updateDate"}, selection, selectionArgs, null, null,
                    null);
            ret = new HashMap<>(c.getCount());
            while (c.moveToNext()) {
                ret.put(c.getInt(0), c.getLong(1));
            }
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return ret;
    }

    /**
     * 0 - Uncategorized
     * -1 - Special (e.g. Starred, Published, Archived, etc.) <- these are categories here o.O
     * -2 - Labels
     * -3 - All feeds, excluding virtual feeds (e.g. Labels and such)
     * -4 - All feeds, including virtual feeds
     */
    public Set<Feed> getFeeds(int categoryId) {
        if (!isDBAvailable())
            return new LinkedHashSet<>();

        String where = null; // categoryId = 0
        if (categoryId >= 0)
            where = "categoryId=" + categoryId;
        switch (categoryId) {
            case -1:
                where = "_id IN (0, -2, -3)";
                break;
            case -2:
                where = "_id < -10";
                break;
            case -3:
                where = "categoryId >= 0";
                break;
            case -4:
                where = null;
                break;
        }

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_FEEDS, null, where, null, null, null, "UPPER(title) ASC");
            Set<Feed> ret = new LinkedHashSet<>(c.getCount());
            while (c.moveToNext()) {
                ret.add(handleFeedCursor(c));
            }
            return ret;
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
    }

    public Set<Category> getAllCategories() {
        if (!isDBAvailable())
            return new LinkedHashSet<>();

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_CATEGORIES, null, "_id>=0", null, null, null, "title ASC");
            Set<Category> ret = new LinkedHashSet<>(c.getCount());
            while (c.moveToNext()) {
                ret.add(handleCategoryCursor(c));
            }
            return ret;
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
    }

    public int getUnreadCount(int id, boolean isCat) {
        if (!isDBAvailable())
            return 0;

        StringBuilder selection = new StringBuilder("isUnread>0");
        String[] selectionArgs = new String[]{String.valueOf(id)};

        if (isCat && id >= 0) {
            // real categories
            selection.append(" and feedId in (select _id from feeds where categoryId=?)");
        } else {
            if (id < 0) {
                // virtual categories
                switch (id) {
                    // All Articles
                    case Data.VCAT_ALL:
                        selectionArgs = null;
                        break;

                    // Fresh Articles
                    case Data.VCAT_FRESH:
                        selection.append(" and updateDate>?");
                        selectionArgs = new String[]{String.valueOf(new Date().getTime()
                                - Controller.getInstance().getFreshArticleMaxAge())};
                        break;

                    // Published Articles
                    case Data.VCAT_PUB:
                        selection.append(" and isPublished>0");
                        selectionArgs = null;
                        break;

                    // Starred Articles
                    case Data.VCAT_STAR:
                        selection.append(" and isStarred>0");
                        selectionArgs = null;
                        break;

                    default:
                        // Probably a label...
                        selection.append(" and feedId=?");
                }
            } else {
                // feeds
                selection.append(" and feedId=?");
            }
        }

        // Read count for given feed
        int ret = 0;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_ARTICLES, new String[]{"count(*)"}, selection.toString(), selectionArgs, null, null,
                    null, null);

            if (c.moveToFirst())
                ret = c.getInt(0);
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }

        return ret;
    }

    @SuppressLint("UseSparseArrays")
    Map<Integer, String> getMarked(String mark, int status) {
        if (!isDBAvailable())
            return new HashMap<>();

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_MARK, new String[]{"id", MARK_NOTE}, mark + "=" + status, null, null, null, null,
                    null);

            Map<Integer, String> ret = new HashMap<>(c.getCount());
            while (c.moveToNext()) {
                ret.put(c.getInt(0), c.getString(1));
            }
            return ret;

        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
    }

    /**
     * remove specified mark in the temporary mark table for specified
     * articles and then cleanup this table
     *
     * @param ids  article IDs, which mark should be reseted
     * @param mark article mark to be reseted
     */
    void setMarked(Map<Integer, String> ids, String mark) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues(1);
            for (String idList : StringSupport.convertListToString(ids.keySet(), 1000)) {
                cv.putNull(mark);
                db.update(TABLE_MARK, cv, "id IN(" + idList + ")", null);
                db.delete(TABLE_MARK, "isUnread IS null AND isStarred IS null AND isPublished IS null", null);
            }

            // Insert notes afterwards and only if given note is not null
            cv = new ContentValues(1);
            for (Integer id : ids.keySet()) {
                String note = ids.get(id);
                if (note == null || note.equals(""))
                    continue;

                cv.put(MARK_NOTE, note);
                db.update(TABLE_MARK, cv, "id=" + id, null);
            }

            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    // *******************************************

    private static Article handleArticleCursor(Cursor c) {
        // @formatter:off
        return new Article(
                c.getInt(0),                        // _id
                c.getInt(1),                        // feedId
                c.getString(2),                     // title
                (c.getInt(3) != 0),                 // isUnread
                c.getString(4),                     // articleUrl
                c.getString(5),                     // articleCommentUrl
                new Date(c.getLong(6)),             // updateDate
                c.getString(7),                     // content
                parseAttachments(c.getString(8)),   // attachments
                (c.getInt(9) != 0),                 // isStarred
                (c.getInt(10) != 0),                // isPublished
                parseArticleLabels(c.getString(12)),// Labels
                c.getString(13)                     // Author
        );
        // @formatter:on
    }

    private static Feed handleFeedCursor(Cursor c) {
        // @formatter:off
        return new Feed(
                c.getInt(0),            // _id
                c.getInt(1),            // categoryId
                c.getString(2),         // title
                c.getString(3),         // url
                c.getInt(4));           // unread
        // @formatter:on
    }

    private static Category handleCategoryCursor(Cursor c) {
        // @formatter:off
        return new Category(
                c.getInt(0),            // _id
                c.getString(1),         // title
                c.getInt(2));           // unread
        // @formatter:on
    }

    private static RemoteFile handleRemoteFileCursor(Cursor c) {
        // @formatter:off
        return new RemoteFile(
                c.getInt(0),            // id
                c.getString(1),         // url
                c.getInt(2),           // length
                new Date(c.getLong(4)), // updateDate
                (c.getInt(5) != 0)     // cached
        );
        // @formatter:on
    }

    private static Set<String> parseAttachments(String att) {
        Set<String> ret = new LinkedHashSet<>();
        if (att == null)
            return ret;

        ret.addAll(Arrays.asList(att.split(";")));
        return ret;
    }

    /*
     * Parse labels from string of the form "label;;label;;...;;label" where each label is of the following format:
     * "caption;forground;background"
     */
    private static Set<Label> parseArticleLabels(String labelStr) {
        Set<Label> ret = new LinkedHashSet<>();
        if (labelStr == null)
            return ret;

        int i = 0;
        for (String s : labelStr.split("---")) {
            String[] l = s.split(";");
            if (l.length > 0) {
                i++;
                Label label = new Label();
                label.id = i;
                label.checked = true;
                label.caption = l[0];
                if (l.length > 1 && l[1].startsWith("#"))
                    label.foregroundColor = l[1];
                if (l.length > 2 && l[1].startsWith("#"))
                    label.backgroundColor = l[2];
                ret.add(label);
            }
        }

        return ret;
    }

    public ArrayList<Article> queryArticlesForImagecache() {
        if (!isDBAvailable())
            return null;

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_ARTICLES, new String[]{"_id", "content", "attachments"},
                    "cachedImages IS NULL AND isUnread>0", null, null, null, null, "1000");

            ArrayList<Article> ret = new ArrayList<>(c.getCount());
            while (c.moveToNext()) {
                Article a = new Article();
                a.id = c.getInt(0);
                a.content = c.getString(1);
                a.attachments = parseAttachments(c.getString(2));
                ret.add(a);
            }
            return ret;
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
    }

    /**
     * insert given remote files into DB and link them with given article
     *
     * @param articleId "parent" article
     * @param fileUrls  array of remote file URLs
     */
    public void insertArticleFiles(int articleId, String[] fileUrls) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            for (String url : fileUrls) {
                long remotefileId = insertRemoteFile(url);
                if (remotefileId != 0)
                    insertRemoteFile2Article(remotefileId, articleId);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    /**
     * get the DB object representing remote file by its URL
     *
     * @param url remote file URL
     * @return remote file object from DB
     */
    private RemoteFile getRemoteFile(String url) {
        if (!isDBAvailable())
            return null;

        RemoteFile rf = null;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_REMOTEFILES, null, "url=?", new String[]{url}, null, null, null, null);
            if (c.moveToFirst())
                rf = handleRemoteFileCursor(c);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return rf;
    }

    /**
     * get remote files for given article
     *
     * @param articleId article, which remote files should be found
     * @return collection of remote file objects from DB or {@code null}
     */
    public Collection<RemoteFile> getRemoteFiles(int articleId) {
        if (!isDBAvailable())
            return null;

        ArrayList<RemoteFile> rfs = null;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            // @formatter:off
            c = db.rawQuery(" SELECT r.*"
                            + " FROM "
                            + TABLE_REMOTEFILES + " r,"
                            + TABLE_REMOTEFILE2ARTICLE + " m, "
                            + TABLE_ARTICLES + " a"
                            + " WHERE m.remotefileId=r.id"
                            + "   AND m.articleId=a._id"
                            + "   AND a._id=?",
                    new String[]{String.valueOf(articleId)});
            // @formatter:on

            rfs = new ArrayList<>(c.getCount());

            while (c.moveToNext()) {
                rfs.add(handleRemoteFileCursor(c));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return rfs;
    }

    /**
     * get remote files for given articles
     *
     * @param whereClause the WHERE clause to apply when selecting.
     * @param whereArgs   You may include ?s in the where clause, which
     *                    will be replaced by the values from whereArgs. The values
     *                    will be bound as Strings.
     * @param uniqOnly    if set to {@code true}, then only remote files, which are referenced by given articles only
     *                    will be
     *                    returned, otherwise all remote files referenced by given articles will be found (even those,
     *                    which are
     *                    referenced also by some other articles)
     * @return collection of remote file objects from DB or {@code null}
     */
    private Collection<RemoteFile> getRemoteFilesForArticles(String whereClause, String[] whereArgs, boolean uniqOnly) {
        if (!isDBAvailable())
            return null;

        ArrayList<RemoteFile> rfs = null;
        StringBuilder uniqRestriction = new StringBuilder();
        String[] queryArgs = whereArgs;

        if (uniqOnly) {
            // @formatter:off
            uniqRestriction.append(
                    " AND m.remotefileId NOT IN (").append(
                    "   SELECT remotefileId").append(
                    "     FROM ").append(
                    TABLE_REMOTEFILE2ARTICLE).append(
                    "           WHERE remotefileId IN (").append(
                    "       SELECT remotefileId").append(
                    "         FROM ").append(
                    TABLE_REMOTEFILE2ARTICLE).append(
                    "         WHERE articleId IN (").append(
                    "           SELECT _id").append(
                    "             FROM ").append(
                    TABLE_ARTICLES).append(
                    "             WHERE ").append(
                    whereClause).append(
                    "           )").append(
                    "         GROUP BY remotefileId)").append(
                    "       AND articleId NOT IN (").append(
                    "         SELECT _id").append(
                    "           FROM ").append(
                    TABLE_ARTICLES).append(
                    "           WHERE ").append(
                    whereClause).append(
                    "       )").append(
                    "   GROUP by remotefileId)");
            // @formatter:on

            // because we are using whereClause twice in uniqRestriction, then we should also extend queryArgs,
            // which will be used in query
            if (whereArgs != null) {
                int initialLength = whereArgs.length;
                queryArgs = new String[initialLength * 3];
                for (int i = 0; i < 3; i++) {
                    System.arraycopy(whereArgs, 0, queryArgs, i * initialLength, initialLength);
                }
            }
        }

        StringBuilder query = new StringBuilder();
        // @formatter:off
        query.append(
                " SELECT r.*").append(
                "   FROM ").append(
                TABLE_REMOTEFILES + " r,").append(
                TABLE_REMOTEFILE2ARTICLE + " m, ").append(
                TABLE_ARTICLES + " a").append(
                "   WHERE m.remotefileId=r.id").append(
                "     AND m.articleId=a._id").append(
                "     AND a._id IN (").append(
                "       SELECT _id FROM ").append(
                TABLE_ARTICLES).append(
                "       WHERE ").append(
                whereClause).append(
                "     )").append(
                uniqRestriction).append(
                "   GROUP BY r.id");
        // @formatter:on

        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            long time = System.currentTimeMillis();
            c = db.rawQuery(query.toString(), queryArgs);

            rfs = new ArrayList<>();

            while (c.moveToNext()) {
                rfs.add(handleRemoteFileCursor(c));
            }
            Log.d(TAG, "Query in getRemoteFilesForArticles took " + (System.currentTimeMillis() - time)
                    + "ms... (remotefiles: " + rfs.size() + ")");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return rfs;
    }

    /**
     * mark given remote file as cached/uncached and optionally specify it's file size
     *
     * @param url    remote file URL
     * @param cached the cached flag
     * @param size   file size may be {@code null}, if so, then it will not be updated in DB
     */
    public void markRemoteFileCached(String url, boolean cached, Long size) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues(2);
            cv.put("cached", cached);
            if (size != null) {
                cv.put("length", size);
            }
            db.update(TABLE_REMOTEFILES, cv, "url=?", new String[]{url});
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    /**
     * mark remote files with given IDs as non cached (cached=0)
     *
     * @param rfIds IDs of remote files to be marked as non-cached
     */
    public void markRemoteFilesNonCached(Collection<Integer> rfIds) {
        if (!isDBAvailable())
            return;

        SQLiteDatabase db = getOpenHelper().getWritableDatabase();
        writeLock(true);
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues(1);
            cv.put("cached", 0);
            for (String ids : StringSupport.convertListToString(rfIds, 1000)) {
                db.update(TABLE_REMOTEFILES, cv, "id in (" + ids + ")", null);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            writeLock(false);
        }
    }

    /**
     * get summary length of remote files, which are cached
     *
     * @return summary length of remote files
     */
    public long getCachedFilesSize() {
        if (!isDBAvailable())
            return 0;

        long ret = 0;
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query(TABLE_REMOTEFILES, new String[]{"SUM(length)"}, "cached=1", null, null, null, null);
            if (c.moveToFirst())
                ret = c.getLong(0);
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return ret;
    }

    /**
     * get remote files which should be deleted to free given amount of space
     *
     * @param spaceToBeFreed amount of space (summary file size) to be freed
     * @return collection of remote files, which can be deleted
     * to free given amount of space
     */
    public Collection<RemoteFile> getUncacheFiles(long spaceToBeFreed) {
        if (!isDBAvailable())
            return null;

        ArrayList<RemoteFile> rfs = new ArrayList<>();
        SQLiteDatabase db = getOpenHelper().getReadableDatabase();
        readLock(true);
        Cursor c = null;
        try {
            c = db.query("remotefile_sequence", null, "cached = 1", null, null, null, "ord");

            long spaceToFree = spaceToBeFreed;
            while (spaceToFree > 0 && c.moveToNext()) {
                RemoteFile rf = handleRemoteFileCursor(c);
                spaceToFree -= rf.length;
                rfs.add(rf);
            }
        } finally {
            if (c != null && !c.isClosed())
                c.close();
            readLock(false);
        }
        return rfs;
    }

    /**
     * delete remote files with given IDs
     *
     * @param idList set of remote file IDs, which should be deleted
     * @return the number of deleted rows
     */
    private int deleteRemoteFiles(Set<Integer> idList) {
        if (!isDBAvailable())
            return 0;

        int deletedCount = 0;
        if (idList != null && !idList.isEmpty()) {
            SQLiteDatabase db = getOpenHelper().getWritableDatabase();
            writeLock(true);
            try {
                for (String ids : StringSupport.convertListToString(idList, 400)) {
                    deletedCount += db.delete(TABLE_REMOTEFILES, "id IN (" + ids + ")", null);
                }
            } finally {
                writeLock(false);
            }
        }
        return deletedCount;
    }
}
